/*
 * XXX: assumption
 * 1. com.oromwin.repos.data.XXXTable className must be equals physical tableName for reflection
 * 2. com.oromwin.repos.data.XXXTable class memeber variables must be start with UpperCase for reflection
 * 3. need to use escape every sql for windows file separator. so use Query instead.
 */
package com.oromwin.repos.common.helper;

import java.io.File;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;

import com.oromwin.repos.common.Logger;
import com.oromwin.repos.common.Query;
import com.oromwin.repos.common.ReposConfig;
import com.oromwin.repos.common.ReposException;
import com.oromwin.repos.data.AbstractReposTable;

public class DBHelper {
    private static Log log = Logger.getLog(DBHelper.class);

    public static boolean isOracle = false;
    public static boolean isMySQL = true; 
    public static boolean isAltibase = false;

    private static String Separator = "`";
    private static final String CRLF = "\n";
    private static final String prefix = "FILE_";

    // Database connection string
    private static Connection conn = null;
    private static String uri = null;
    private static String host = null;
    private static String dbname = null;
    private static int port = 0;
    private static String user = null;
    private static String passwd = null;
    private static int timeout = 0;
    private static String curi = null;

    static {
        try {
            String className = ReposConfig.getInstance().getReposDatabaseDriver();
            isOracle = className.equals("oracle.jdbc.driver.OracleDriver");
            isMySQL = className.equals("org.gjt.mm.mysql.Driver");
            if (!isMySQL) Separator = "";
            isAltibase = className.equals("Altibase.jdbc.driver.AltibaseDriver");
            Class.forName(className);
            // initialize database connection string
            ReposConfig conf = ReposConfig.getInstance();
            uri = conf.getReposDatabaseUri();
            host = conf.getReposDatabaseHost();
            dbname = conf.getReposDatabaseName();
            port = conf.getReposDatabasePort();
            user = conf.getReposDatabaseUser();
            passwd = conf.getReposDatabasePasswd();
            timeout = conf.getReposDatabaseConnectTimeout();
            curi = String.format(uri, host, port, dbname);
        } catch (Exception e) {
            Logger.errorWithThrowable(log, e, "DBHelper.constructor()");
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection() {
        String message = null;
        while (conn == null) {
            try {
                conn = DriverManager.getConnection(curi, user, passwd);
            	message = "Succeeded to create initial database connection.";
            	if (log.isDebugEnabled()) log.debug(message);
            } catch (SQLException e) {
                message = String.format("Failed to connect database (#1), reconnect after {%d} seconds.", timeout);
                if (log.isErrorEnabled()) log.error(message);
                try {
                    Thread.sleep(timeout);
                } catch (InterruptedException e1) {
                    Logger.errorWithThrowable(log, e, "DBHelper.getConnection(#1)");
                }
            }
        }
        while (conn != null) {
            try {
                if (!conn.isClosed()) {
                    break;
                } else {
                    message = String.format("Failed to connect database (#2), reconnect after {%d} milli seconds.", timeout);
                    if (log.isErrorEnabled()) log.error(message);
                    try {
                        Thread.sleep(timeout);
                        conn = DriverManager.getConnection(curi, user, passwd);
                    } catch (InterruptedException ie) {
                        Logger.errorWithThrowable(log, ie, "DBHelper.getConnection(#2)");
                    }
                }
            } catch (SQLException e) {
                Logger.errorWithThrowable(log, e, "DBHelper.getConnection(#3)");
                message = String.format("Failed to connect database (#3), reconnect after {%d} milli seconds.", timeout);
                if (log.isErrorEnabled()) log.error(message);
                try {
                    Thread.sleep(timeout);
                    conn = DriverManager.getConnection(curi, user, passwd);
                } catch (InterruptedException ie) {
                    Logger.errorWithThrowable(log, ie, "DBHelper.getConnection(#4)");
                } catch (SQLException se) {
                    Logger.errorWithThrowable(log, se, "DBHelper.getConnection(#5)");
                }
            }
        }
        return conn;
    }

    public static int getRecordCount(ResultSet rs) throws SQLException {
        int count = 0;
        try {
            rs.last();
            count = rs.getRow();
            rs.beforeFirst();
        } catch (SQLException e) {
            Logger.errorWithThrowable(log, e, "DBHelper.getRecordCount()");
        }
        return count;
    }

    public static Statement createStatement() throws SQLException {
        return getConnection().createStatement();
    }

    /**
     * @deprecated
     * @param sql
     * @return
     * @throws SQLException
     */
    public static ResultSet execute(String sql) throws SQLException {
        return getConnection().createStatement().executeQuery(sql);
    }

    /**
     * @deprecated
     * @param sql
     * @throws SQLException
     */
    public static void update(String sql) throws SQLException {
        getConnection().createStatement().executeUpdate(sql);
    }

    public static boolean createDatabase() {
        String sql = "CREATE DATABASE IF NOT EXISTS `repository` DEFAULT CHARSET=utf8";
        Statement stmt = null;
        try {
            stmt = createStatement();
            stmt.executeUpdate(sql);
            return true;
        } catch (SQLException e) {
            ReposException.create(log, e);
            return false;
        }
    }

    public static boolean createAndGrantUser(String user, String password) {
        Statement stmt = null;
        if (isMySQL) {
            String sql = "GRANT ALL PRIVILEGES ON `repository`.* TO '" + user + "'@'localhost' IDENTIFIED BY '" + password + "' WITH GRANT OPTION;";
            if (log.isDebugEnabled()) log.debug(sql);
            try {
                stmt = createStatement();
                stmt.executeUpdate(sql);
                return true;
            } catch (SQLException e) {
                ReposException.create(log, e);
                return false;
            }
        } else {
            return false;
        }
    }

    public static boolean dropTables(boolean execute) {
        Statement stmt = null;
        if (isMySQL) {
            try {
                if (execute) stmt = createStatement();
                String sql = "DROP TABLE IF EXISTS `" + prefix + "FileTable`;";
                if (log.isDebugEnabled()) log.debug(sql);
                if (execute) stmt.executeUpdate(sql);
                sql = "DROP TABLE IF EXISTS `" + prefix + "MediaTable`;";
                if (log.isDebugEnabled()) log.debug(sql);
                if (execute) stmt.executeUpdate(sql);
                sql = "DROP TABLE IF EXISTS `" + prefix + "VolumeTable`;";
                if (log.isDebugEnabled()) log.debug(sql);
                if (execute) stmt.executeUpdate(sql);
                return true;
            } catch (SQLException e) {
                ReposException.create(log, e);
                return false;
            }
        } else {
            return false;
        }
    }

    public static boolean createVolumeTable(boolean execute) {
        Statement stmt = null;
        if (isMySQL) {
            String sql = "CREATE TABLE IF NOT EXISTS `" + prefix + "VolumeTable` ( " + CRLF +
            "`VolumeId` VARCHAR(100) NOT NULL, " + CRLF +
            "`VolumeName` VARCHAR(255) NOT NULL DEFAULT ''," + CRLF +
            "`VolumeStatus` INT(11) NOT NULL DEFAULT 0, " + CRLF +
            "`VolumeCreateDate` CHAR(14) NOT NULL DEFAULT '19710101000000'," + CRLF +
            "`VolumeUpdateDate` CHAR(14) NOT NULL DEFAULT '19710101000000'," + CRLF +
            "PRIMARY KEY (VolumeId)" + CRLF +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;" + CRLF;
            log.info(sql);
            try {
                if (execute) stmt = createStatement();
                if (execute) stmt.executeUpdate(sql);
                return true;
            } catch (SQLException e) {
                ReposException.create(log, e);
                return false;
            }
        } else {
            return false;
        }
    }

    public static boolean createMediaTable(boolean execute) {
        Statement stmt = null;
        if (isMySQL) {
            String sql = "CREATE TABLE IF NOT EXISTS `" + prefix + "MediaTable` ( " + CRLF +
            "`VolumeId` VARCHAR(100) NOT NULL, " + CRLF +
            "`MediaId` VARCHAR(100) NOT NULL, " +  CRLF +
            "`MediaPath` VARCHAR(255) NOT NULL, " +  CRLF +
            "`MediaMaxBytes` BIGINT(20) NOT NULL DEFAULT '0'," +  CRLF +
            "`MediaUsedBytes` BIGINT(20) NOT NULL DEFAULT '0'," +  CRLF +
            "`MediaFileCount` INT(11) NOT NULL DEFAULT '0', " +  CRLF +
            "`MediaStatus` INT(11) NOT NULL DEFAULT 0, " +  CRLF +
            "`MediaCreateDate` CHAR(14) NOT NULL DEFAULT '19710101000000'," +  CRLF +
            "`MediaUpdateDate` CHAR(14) NOT NULL DEFAULT '19710101000000'," +  CRLF +
            "FOREIGN KEY ( `VolumeId` ) REFERENCES `" + prefix + "VolumeTable` ( `VolumeId` ) ON UPDATE CASCADE ON DELETE CASCADE, " + CRLF + 
            "PRIMARY KEY ( `MediaId` )" + CRLF +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;" + CRLF;
            log.info(sql);
            try {
                if (execute) stmt = createStatement();
                if (execute) stmt.executeUpdate(sql);
                return true;
            } catch (SQLException e) {
                ReposException.create(log, e);
                return false;
            }
        } else {
            return false;
        }
    }

    public static boolean createFileTable(boolean execute) {
        Statement stmt = null;
        if (isMySQL) {
            String sql = "CREATE TABLE IF NOT EXISTS `" + prefix + "FileTable` ( " + CRLF +
            "`VolumeId` VARCHAR(100) NOT NULL, " + CRLF +
            "`MediaId` VARCHAR(100) NOT NULL, " +  CRLF +
            "`FileId` VARCHAR(100) NOT NULL, " +  CRLF +
            "`FileName` VARCHAR(100) NOT NULL DEFAULT '', " + CRLF + 
            "`FileFormat` VARCHAR(100) NOT NULL DEFAULT '', " + CRLF + 
            "`FilePath` VARCHAR(255) NOT NULL DEFAULT ''," + CRLF + 
            "`FileBytes` BIGINT(20) NOT NULL DEFAULT '0'," + CRLF + 
            "`FileCategory` VARCHAR(100) NULL DEFAULT ''," + CRLF + 
            "`FileStatus` INT(11) NOT NULL DEFAULT 0, " + CRLF + 
            "`FilePdfConvFlag` VARCHAR(10) NOT NULL DEFAULT '0', " +  CRLF +
            "`FileCompressionFlag` VARCHAR(10) NOT NULL DEFAULT '0', " + CRLF + 
            "`FileCreateDate` CHAR(14) NOT NULL DEFAULT '19710101000000'," + CRLF + 
            "`FileUpdateDate` CHAR(14) NOT NULL DEFAULT '19710101000000'," + CRLF + 
            "FOREIGN KEY ( `VolumeId` ) REFERENCES `" + prefix + "VolumeTable` ( `VolumeId` ) ON UPDATE CASCADE ON DELETE CASCADE, " + CRLF + 
            "FOREIGN KEY ( `MediaId` ) REFERENCES `" + prefix + "MediaTable` ( `MediaId` ) ON UPDATE CASCADE ON DELETE CASCADE, " + CRLF + 
            "PRIMARY KEY ( `FileId` )" + CRLF +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;" + CRLF;
            log.info(sql);
            try {
                if (execute) stmt = createStatement();
                if (execute) stmt.executeUpdate(sql);
                return true;
            } catch (SQLException e) {
                ReposException.create(log, e);
                return false;
            }
        } else {
            return false;
        }
    }

    /**
     * @param tableClass
     * @return
     */
    private static ArrayList<Field> getUsableFields(Class<? extends AbstractReposTable> tableClass) {
        ArrayList<Field> fields = new ArrayList<Field>();
        for (Field field : tableClass.getDeclaredFields()) {
            if (field.getName().compareTo("A") >= 0 && field.getName().compareTo("Z") <= 0) {
                fields.add(field);
            }
        }
        return fields;
    }

    public static void insertTableByStatement(AbstractReposTable table) throws ReposException {
        PreparedStatement pstmt = null;
        try {
            ArrayList<Field> fields = getUsableFields(table.getClass());
            // generating insert query
            String className = table.getClass().getSimpleName();
            Query query = new Query("INSERT INTO " + prefix + className + " ( ");
            for (Field field : fields) {
                query.append(String.format("%s%s%s%s", Separator, field.getName(), Separator, ", "));
            }
            query.delete(2);
            query.append(" ) VALUES ( ");
            for (int i = 0; i < fields.size(); i++) {
                query.append("?, ");
            }
            query.delete(2);
            query.append(" );");
            // generating prepared statement 
            pstmt = getConnection().prepareStatement(query.toString());
            for (int i = 0, j = 1; i < fields.size(); i++) {
                Field field = fields.get(i);
                String methodName = "get" + field.getName();
                try {
                    Method method = table.getClass().getDeclaredMethod(methodName);
                    if (fields.get(i).getType() == String.class) {
                        pstmt.setString(j++, (String) method.invoke(table));
                    } else if (fields.get(i).getType() == Long.class){
                        pstmt.setLong(j++, (Long) method.invoke(table));
                    } else if (fields.get(i).getType() == Integer.class){
                        pstmt.setInt(j++, (Integer) method.invoke(table));
                    } else if (fields.get(i).getType() == Timestamp.class) {
                        pstmt.setTimestamp(j++, (Timestamp) method.invoke(table));
                    } else if (fields.get(i).getType() == Boolean.class) {
                        pstmt.setBoolean(j++, (Boolean) method.invoke(table));
                    } else if (fields.get(i).getType() == File.class) {
                        pstmt.setString(j++, method.invoke(table).toString());
                    }
                } catch (IllegalAccessException e) {
                    String message = String.format("Illegal class method definition, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                } catch (NoSuchMethodException e) {
                    String message = String.format("Illegal class method name, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                } catch (InvocationTargetException e) {
                    String message = String.format("Illegal class method target, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                }
            }
            if (log.isDebugEnabled()) log.debug(query.toString());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw ReposException.create(log, e);
        } finally {
            try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
        }
    }

    public static void insertTable(AbstractReposTable table) throws ReposException {
        PreparedStatement pstmt = null;
        try {
            ArrayList<Field> fields = getUsableFields(table.getClass());
            // generating insert query
            String className = table.getClass().getSimpleName();
            Query query = new Query("INSERT INTO " + prefix + className + " ( ");
            for (Field field : fields) {
                query.append(String.format("%s%s%s%s", Separator, field.getName(), Separator, ", "));
            }
            query.delete(2);
            query.append(" ) VALUES ( ");

            for (int i = 0; i < fields.size(); i++) {
                Field field = fields.get(i);
                String methodName = "get" + field.getName();
                try {
                    Method method = table.getClass().getDeclaredMethod(methodName);
                    if (fields.get(i).getType() == String.class) {
                        String value = (String) method.invoke(table);
                        query.append("'").append(value.trim()).append("', ");
                    } else if (fields.get(i).getType() == Long.class){
                        Long value = (Long) method.invoke(table);
                        query.append(value.toString()).append(", ");;
                    } else if (fields.get(i).getType() == Integer.class){
                        Integer value = (Integer) method.invoke(table);
                        query.append(value.toString()).append(", ");;
                    } else if (fields.get(i).getType() == Timestamp.class) {
                        Timestamp value = (Timestamp) method.invoke(table);
                        query.append("'").append(value.toString()).append("', ");
                    } else if (fields.get(i).getType() == Boolean.class) {
                        Boolean value = (Boolean) method.invoke(table);
                        if (value != null) {
                            Integer status = new Integer(( value == true ? 1 : 0 )); // 1 = deleted, 0 = created
                            query.append("'").append(status.toString()).append("', ");
                        }
                    } else if (fields.get(i).getType() == File.class) {
                        File value = (File) method.invoke(table);
                        query.append("'").append(value).append("', ");
                    } else {
                        String value = (String) method.invoke(table);
                        query.append("'").append(value.trim()).append("', ");
                    }
                } catch (IllegalAccessException e) {
                    String message = String.format("Illegal class method definition, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                } catch (NoSuchMethodException e) {
                    String message = String.format("Illegal class method name, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                } catch (InvocationTargetException e) {
                    String message = String.format("Illegal class method target, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                }
            }
            query.delete(2);
            query.append(" );");
            if (log.isDebugEnabled()) log.debug(query.toString());
            // generating prepared statement 
            pstmt = getConnection().prepareStatement(query.toString());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw ReposException.create(log, e);
        } finally {
            try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
        }
    }

    public static List<AbstractReposTable> selectTable(Class<? extends AbstractReposTable> tableClass, String whereClause) throws ReposException {
        List<AbstractReposTable> reposTables = new ArrayList<AbstractReposTable>();
        ArrayList<Field> fields = getUsableFields(tableClass);
        // generating select query
        String className = tableClass.getSimpleName();
        Query query = new Query("SELECT ");
        for (Field field : fields) {
            query.append(String.format("%s%s%s%s", Separator, field.getName(), Separator, ", "));
        }
        query.delete(2);
        query.append(" FROM " + prefix + className);
        if (whereClause != null) {
            query.append(" WHERE " + whereClause);
        }
        query.append(";");
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // generating prepared statement 
        try {
            pstmt = getConnection().prepareStatement(query.toString());
            if (log.isDebugEnabled()) log.debug(query.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                AbstractReposTable reposTable = tableClass.newInstance();
                for (int i = 0; i < fields.size(); i++) {
                    Field field = fields.get(i);
                    String fieldName = field.getName();
                    String methodName = "set" + fieldName;
                    try {
                        Method method = null;
                        if (fields.get(i).getType() == String.class) {
                            method = tableClass.getMethod(methodName, String.class);
                            String value = rs.getString(fieldName);
                            method.invoke(reposTable, value);
                        } else if (fields.get(i).getType() == Long.class){
                            method = tableClass.getMethod(methodName, Long.class);
                            Long value = rs.getLong(fieldName);
                            method.invoke(reposTable, value);
                        } else if (fields.get(i).getType() == Integer.class){
                            method = tableClass.getMethod(methodName, Integer.class);
                            Integer value = rs.getInt(fieldName);
                            method.invoke(reposTable, value);
                        } else if (fields.get(i).getType() == Timestamp.class) {
                            method = tableClass.getMethod(methodName, Timestamp.class);
                            Timestamp value = rs.getTimestamp(fieldName);
                            method.invoke(reposTable, value);
                        } else if (fields.get(i).getType() == Boolean.class) {
                            method = tableClass.getMethod(methodName, Boolean.class);
                            Boolean value = rs.getBoolean(fieldName);
                            method.invoke(reposTable, value);
                        } else if (fields.get(i).getType() == File.class) {
                            method = tableClass.getMethod(methodName, File.class);
                            String value = rs.getString(fieldName);
                            method.invoke(reposTable, new File(value));
                        } else {
                            method = tableClass.getMethod(methodName, String.class);
                            String value = rs.getString(fieldName);
                            method.invoke(reposTable, value);
                        }

                    } catch (IllegalAccessException e) {
                        String message = String.format("Illegal class method definition, class:method {%s:%s}", className, methodName);
                        throw ReposException.create(log, e, message);
                    } catch (NoSuchMethodException e) {
                        String message = String.format("Illegal class method name, class:method {%s:%s}", className, methodName);
                        throw ReposException.create(log, e, message);
                    } catch (InvocationTargetException e) {
                        String message = String.format("Illegal class method target, class:method {%s:%s}", className, methodName);
                        throw ReposException.create(log, e, message);
                    } catch (IllegalArgumentException e) {
                        String message = String.format("Illegal method argument, reposTable:fieldName {%s:%s}", reposTable, fieldName);
                        throw ReposException.create(log, e, message);
                    } catch (SecurityException e) {
                        String message = String.format("Illegal method access, methodName:className {%s:%s}", methodName, className);
                        throw ReposException.create(log, e, message);
                    }
                }
                reposTables.add(reposTable);
            }
        } catch (InstantiationException e) {
            String message = String.format("No default constructor in class, class:stmt {%s:%s}", className, query.toString());
            throw ReposException.create(log, e, message);
        } catch (IllegalAccessException e) {
            String message = String.format("Illegal instance access, class:stmt {%s:%s}", className, query.toString());
            throw ReposException.create(log, e, message);
        } catch (SQLException e) {
            throw ReposException.create(log, e, query.toString());
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {}
            try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
        }
        return reposTables;
    }

    public static void updateTable(AbstractReposTable table, String whereClause) throws ReposException {
        PreparedStatement pstmt = null;
        try {
            ArrayList<Field> fields = getUsableFields(table.getClass());
            // generating insert query
            String className = table.getClass().getSimpleName();
            Query query = new Query("UPDATE " + prefix + className + " SET ");
            for (int i = 0; i < fields.size(); i++) {
                Field field = fields.get(i);
                String methodName = "get" + field.getName();
                try {
                    Method method = table.getClass().getDeclaredMethod(methodName);
                    if (fields.get(i).getType() == String.class) {
                        String value = (String) method.invoke(table);
                        if (value != null) {
                            query.append(String.format("%s%s%s = '%s', ", Separator, field.getName(), Separator, value.trim()));
                        }
                    } else if (fields.get(i).getType() == Long.class){
                        Long value = (Long) method.invoke(table);
                        if (value != null) {
                            query.append(String.format("%s%s%s = %d, ", Separator, field.getName(), Separator, value));
                        }
                    } else if (fields.get(i).getType() == Integer.class){
                        Integer value = (Integer) method.invoke(table);
                        if (value != null) {
                            query.append(String.format("%s%s%s = %d, ", Separator, field.getName(), Separator, value));
                        }
                    } else if (fields.get(i).getType() == Timestamp.class) {
                        Timestamp value = (Timestamp) method.invoke(table);
                        if (value != null) {
                            query.append(String.format("%s%s%s = '%d', ", Separator, field.getName(), Separator, value));
                        }
                    } else if (fields.get(i).getType() == Boolean.class) {
                        Boolean value = (Boolean) method.invoke(table);
                        if (value != null) {
                            int status = ( value == true ? 1 : 0 ); // 1 = deleted, 0 = created
                            query.append(String.format("%s%s%s = '%d', ", Separator, field.getName(), Separator, status));
                        }
                    } else if (fields.get(i).getType() == File.class) {
                        File value = (File) method.invoke(table);
                        if (value != null) {
                            query.append(String.format("%s%s%s = '%s', ", Separator, field.getName(), Separator, value.toString()));
                        }
                    } else {
                        String value = (String) method.invoke(table);
                        if (value != null) {
                            query.append(String.format("%s%s%s = '%s', ", Separator, field.getName(), Separator, value.trim()));
                        }
                    }

                } catch (IllegalAccessException e) {
                    String message = String.format("Illegal class method definition, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                } catch (NoSuchMethodException e) {
                    String message = String.format("Illegal class method name, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                } catch (InvocationTargetException e) {
                    String message = String.format("Illegal class method target, class:method {%s:%s}", className, methodName);
                    throw ReposException.create(log, e, message);
                }
            }
            query.delete(2);
            if (whereClause != null) {
                query.append(" WHERE " + whereClause);
            }
            query.append(";");
            // generating prepared statement 
            pstmt = getConnection().prepareStatement(query.toString());
            if (log.isDebugEnabled()) log.debug(query.toString());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw ReposException.create(log, e);
        } finally {
            try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
        }
    }

    public static void deleteTable(Class<? extends AbstractReposTable> tableClass, String whereClause) throws ReposException {
        PreparedStatement pstmt = null;
        try {
            // generating delete query
            String className = tableClass.getSimpleName();
            Query query = new Query("DELETE FROM " + prefix + className);
            if (whereClause != null) {
                query.append(" WHERE " + whereClause);
            }
            query.append(";");
            // generating prepared statement 
            pstmt = getConnection().prepareStatement(query.toString());
            if (log.isDebugEnabled()) log.debug(query.toString());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw ReposException.create(log, e);
        } finally {
            try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
        }
    }
}
